﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using ReportWizard.Helpers;
using ReportWizard.Properties;

namespace ReportWizard.DataAccess
{
    public class WordTemplate : IComparer<WordTemplate>
    {
        public int Id { get; set; }
        public DateTime DateCreated { get; private set; }
        public bool Visible { get; set; }
        public bool Deleted { get; private set; }

        public byte[] Template { get; set; }
        public string FileName { get; set; }
        public string Description { get; set; }
        public List<WordBookmark> DocumentBookmarks { get; set; }
        public List<BookmarkGroup> BookmarkGroups { get; set; }

        public WordTemplate()
        {
            
        }

        public WordTemplate(byte[] template, string fileName, string description, bool visible = true)
        {
            Template = template;
            FileName = fileName;
            Description = description;
            Visible = visible;
        }

        public WordTemplate(int id, byte[] template, string fileName, string description, bool visible = true)
        {
            Id = id;
            Template = template;
            FileName = fileName;
            Description = description;
            Visible = visible;
        }

        public void Insert()
        {
            try
            {
                if (!IsExist())
                {
                    using (SqlConnection con = CommonDb.GetOilWizardConection())
                    {

                        SqlCommand saveCmd = new SqlCommand("WordTemplateInsert", con);
                        saveCmd.CommandType = CommandType.StoredProcedure;

                        saveCmd.Parameters.Add("@Template", SqlDbType.VarBinary).Value = Template;
                        saveCmd.Parameters.Add("@FileName", SqlDbType.VarBinary).Value = Crypto.Encrypt(FileName);

                        saveCmd.Parameters.Add("@Description", SqlDbType.VarBinary).Value =
                            Crypto.Encrypt(!string.IsNullOrEmpty(Description) ? Description : string.Empty);

                        saveCmd.Parameters.Add("@Visible", SqlDbType.Bit).Value = Visible;

                        saveCmd.Parameters.Add("@Id", SqlDbType.Int).Direction = ParameterDirection.Output;

                        con.Open();

                        bool isRowsAffected = saveCmd.ExecuteNonQuery() > 0;
                        if (isRowsAffected)
                        {
                            Id = Convert.ToInt32(saveCmd.Parameters["@Id"].Value);

                            HistoryRecord hr = new HistoryRecord
                            {
                                Action = HistoryEvent.TemlateAdd,
                                Description =
                                    string.Format(
                                        "Добавлен шаблон [{0}]", FileName)
                            };
                            hr.Insert();
                        }
                    }
                }
                else
                {
                    ShowMessage.Error(Resources.SameNameTemplateIsExist);
                }
            }
            catch (Exception ex)
            {
                Logger.WriteLog(ex);
            }
        }

        public bool Update()
        {
            try
            {
                if (!IsExist())
                {
                    using (SqlConnection con = CommonDb.GetOilWizardConection())
                    {
                        SqlCommand updCmd = new SqlCommand("WordTemplateUpdate", con);
                        updCmd.CommandType = CommandType.StoredProcedure;

                        updCmd.Parameters.Add("@Id", SqlDbType.Int).Value = Id;
                        updCmd.Parameters.Add("@FileName", SqlDbType.VarBinary).Value = Crypto.Encrypt(FileName);

                        updCmd.Parameters.Add("@Description", SqlDbType.VarBinary).Value =
                            Crypto.Encrypt(!string.IsNullOrEmpty(Description) ? Description : string.Empty);

                        updCmd.Parameters.Add("@Visible", SqlDbType.Bit).Value = Visible;

                        con.Open();
                        bool isRowsAffected = updCmd.ExecuteNonQuery() > 0;
                        if (isRowsAffected)
                        {
                            HistoryRecord hr = new HistoryRecord
                            {
                                Action = HistoryEvent.TemplateChange,
                                Description = string.Format("Изменён шаблон [{0}]", FileName)
                            };
                            hr.Insert();
                        }

                        return isRowsAffected;
                    }
                }
                else
                {
                    ShowMessage.Error(Resources.SameNameTemplateIsExist);
                    return false;
                }
            }
            catch (Exception ex)
            {
                Logger.WriteLog(ex);
                return false;
            }
        }

        public static bool Delete(int id)
        {
            try
            {
                using (SqlConnection con = CommonDb.GetOilWizardConection())
                {
                    SqlCommand delCmd = new SqlCommand("WordTemplateDelete", con);
                    delCmd.CommandType = CommandType.StoredProcedure;

                    delCmd.Parameters.Add("@Id", SqlDbType.Int).Value = id;

                    con.Open();
                    bool isRowsAffected = delCmd.ExecuteNonQuery() > 0;
                    if (isRowsAffected)
                    {
                        WordTemplate wt = Select(id);
                        if (wt != null)
                        {
                            HistoryRecord hr = new HistoryRecord
                            {
                                Action = HistoryEvent.TemplateRemove,
                                Description = string.Format("Удалён шаблон [{0}]", wt.FileName)
                            };
                            hr.Insert();
                        }
                    }

                    return isRowsAffected;
                }
            }
            catch (Exception ex)
            {
                Logger.WriteLog(ex);
                return false;
            }
        }

        public static WordTemplate Select(int id, bool onlyVisibleBookmarks = true)
        {
            try
            {
                using (SqlConnection con = CommonDb.GetOilWizardConection())
                {
                    SqlCommand getCmd = new SqlCommand("WordTemplateSelect", con);
                    getCmd.CommandType = CommandType.StoredProcedure;

                    getCmd.Parameters.Add("@Id", SqlDbType.Int).Value = id;

                    con.Open();
                    List<WordTemplate> cList = MapFrom(getCmd.ExecuteReader());
                    
                    if (cList != null)
                    {
                        WordTemplate wt = cList.FirstOrDefault();
                        if (wt != null)
                        {
                            wt.DocumentBookmarks = WordBookmark.Select(id, onlyVisibleBookmarks);
                            wt.BookmarkGroups = BookmarkGroup.SelectInTemplate(id);
                        }

                        return wt;
                    }
                    return null;
                }
            }
            catch (Exception ex)
            {
                Logger.WriteLog(ex);
                return null;
            }
        }

        public static List<WordTemplate> Select(bool onlyVisible = true)
        {
            try
            {
                using (SqlConnection con = CommonDb.GetOilWizardConection())
                {
                    SqlCommand getCmd = new SqlCommand("WordTemplateSelectAll", con);
                    getCmd.CommandType = CommandType.StoredProcedure;

                    getCmd.Parameters.Add("@OnlyVisible", SqlDbType.Bit).Value = onlyVisible;

                    con.Open();
                    return MapFrom(getCmd.ExecuteReader());
                }
            }
            catch (Exception ex)
            {
                Logger.WriteLog(ex);
                return null;
            }
        }

        private bool IsExist()
        {
            try
            {
                using (SqlConnection con = CommonDb.GetOilWizardConection())
                {
                    SqlCommand getCmd = new SqlCommand("WordTemplateIsExist", con);
                    getCmd.CommandType = CommandType.StoredProcedure;

                    getCmd.Parameters.Add("@FileName", SqlDbType.VarBinary).Value = Crypto.Encrypt(FileName);
                    getCmd.Parameters.Add("@Id", SqlDbType.Int).Value = Id;

                    con.Open();
                    return Convert.ToInt32(getCmd.ExecuteScalar()) > 0;
                }
            }
            catch (Exception ex)
            {
                Logger.WriteLog(ex);
                return true;
            }
        }

        private static List<WordTemplate> MapFrom(SqlDataReader dr)
        {
            try
            {
                if (dr != null && dr.HasRows)
                {
                    List<WordTemplate> acList = new List<WordTemplate>();
                    while (dr.Read())
                    {
                        WordTemplate wt = new WordTemplate
                                              {
                                                  Id = Convert.ToInt32(dr["Id"]),
                                                  Template = (byte[])dr["Template"],
                                                  FileName = Crypto.Decrypt((byte[])dr["FileName"]),
                                                  DateCreated = Convert.ToDateTime(dr["DateCreated"]),
                                                  Description = Crypto.Decrypt((byte[])dr["Description"]),
                                                  Visible = Convert.ToBoolean(dr["Visible"]),
                                                  Deleted = Convert.ToBoolean(dr["Deleted"])
                                              };

                        acList.Add(wt);
                    }
                    acList.Sort(new WordTemplate());
                    return acList;
                }
                return null;
            }
            catch (Exception ex)
            {
                Logger.WriteLog(ex);
                return null;
            }
        }

        public int Compare(WordTemplate x, WordTemplate y)
        {
            return x.FileName.CompareTo(y.FileName);
        }

        public override string ToString()
        {
            return FileName;
        }
    }
}
